*************************************************************************************
************** Project: Robots and Household Financial Behavior *********************
***************************  Gomes, Jansson and Karabulut ***************************
************************** COMPUTING THE SAVING RATES *******************************
*************************************************************************************
****************************** LAST MODIFIED ****************************************
******************************** 2023-06-07 *****************************************
** Stata Settings ***
cap clear all
macro drop _all
clear
set mem 1000m
set matsize 11000
set maxvar 10000
set more off
**Define Paths**
global data "DEFINE THE PATH HERE"
global projf "DEFINE THE PATH HERE"
*** Uploading the Dataset ***
**Saving a log file**
cap log close
log using "$projf/logs/2b_Return_Calculation_updated_230423.log", replace
**Construct the working Sample for Panel Analysis**
*use "$projf/data/linda_sample200424_1999.dta", clear
use "$projf/data/linda_sample230423_1999.dta", clear
forvalues i=2000(1)2007 {
**Upload the Working Sample**
append using "$projf/data/linda_sample230423_`i'.dta"
}
**Define a Panel and Time Variable**
sort idhh year
tsset idhh year
**Calculate the total SEK changes in the stock portfolio**
/*The 1999-value will be missing by construction */
bys idhh: gen diff_stock=(d.hhstocksmutual)
la var diff_stock "SEK chg in stock port"
**Calculate the one-year buy-and-hold profits/losses on the stock portfolio**
/* Assumption is that inflows/outflows and rebalancing take place at the end of the year */
*Define the SEK value of the stock portfolio from the previous period**
bys idhh: gen Lstock=(L.hhstocksmutual)
*Calcuate the buy-and-hold profits/losses
bys idhh: gen pas_chages=(Lstock*[return_lag1-1])
/*Note: Passive changes will be missing if the household holds no stocks in his portfolio */
la var pas_chages "SEK profits/losses"
**Calculate the other changes (i.e., inflows, outflows, etc.)**
bys idhh: gen act_chages=(diff_stock-pas_chages)
*Redefine active changes when passive changes are missing (no stock or stock mutual fund holding)
replace act_chages=diff_stock if mi(pas_chages)==1
la var act_chages "SEK inflows, outflows, etc."
/* by construction, sum of active and passive changes should give the diff_stock*/
**Summarize the variables of interest**
su diff_stock pas_chages act_chages
**Calculate the aggregate active and passive changes over the sample period**
/*Note that we do not consider the missing values in the calculation - if all values are missing, then this variable is also missing*/
bys idhh: egen tot_pas_changes=total(pas_chages),missing
bys idhh: egen tot_act_changes=total(act_chages),missing
*Calculate the sum of active & passive changes over the sample period 
/* this should be equal to the difference in hhstocksmutual between 1999-2007 */
gen tot_diff_99_07=(tot_pas_changes+tot_act_changes)
**Change in the Annual Financial Wealth**
/*The 1999-value will be missing by construction */
bys idhh: gen diff_finw=(d.hhfinw)
la var diff_finw "SEK chg in stock port"
**Calculate the active changes in the financial wealth**
bys idhh: gen act_finw=(diff_finw-pas_chages)
*Redefine active changes in fin wealth when passive changes are missing (no stock or stock mutual fund holding)
replace act_finw=diff_finw if mi(pas_chages)==1
**Calculating the annual active saving rates - based on financial wealth **
bys idhh: gen act_sav_rate_inc=(act_finw/inchh)
*Winsor the saving rate at 1% - Exclude any missing values*
winsor act_sav_rate_inc if mi(act_sav_rate_inc)==0, gen(wact_sav_rate_inc) p(.01)
replace act_sav_rate_inc=wact_sav_rate_inc
drop wact_sav_rate_inc
su act_sav_rate_inc,d

**Define the active saving rate in Year 2000 (the first available year)
gen act_sav_rate_inc_00=(act_sav_rate_inc) if year==2000
replace act_sav_rate_inc_00=0 if mi(act_sav_rate_inc_00)==1 & year==2000
bys idhh: egen mean_act_sav_rate_inc_00=mean(act_sav_rate_inc_00) 
replace act_sav_rate_inc_00=mean_act_sav_rate_inc_00
drop mean_act_sav_rate_inc_00
su act_sav_rate_inc_00, d

**Calculate the average active saving rate between 2000-2007**
drop if year==1999
bys idhh: egen mean_act_sav_rate_00_07=mean(act_sav_rate_inc)
su mean_act_sav_rate_00_07,d

**Calculate the "cumulative" active saving rates**
gen act_sav_rate=(act_sav_rate_inc+1)
/*Set the missing saving rate to zero when calculating the cumulative saving rate */
replace act_sav_rate=1 if mi(act_sav_rate_inc)==1 /* assuming zero return */
**
bys idhh: gen cum_sav_00_07=(act_sav_rate*L.act_sav_rate*L2.act_sav_rate*L3.act_sav_rate*L4.act_sav_rate*L5.act_sav_rate*L6.act_sav_rate*L7.act_sav_rate)-1
bys idhh: gen cum_sav_01_07=(act_sav_rate*L.act_sav_rate*L2.act_sav_rate*L3.act_sav_rate*L4.act_sav_rate*L5.act_sav_rate*L6.act_sav_rate)-1
bys idhh: gen cum_sav_02_07=(act_sav_rate*L.act_sav_rate*L2.act_sav_rate*L3.act_sav_rate*L4.act_sav_rate*L5.act_sav_rate)-1
bys idhh: gen cum_sav_03_07=(act_sav_rate*L.act_sav_rate*L2.act_sav_rate*L3.act_sav_rate*L4.act_sav_rate)-1
bys idhh: gen cum_sav_04_07=(act_sav_rate*L.act_sav_rate*L2.act_sav_rate*L3.act_sav_rate)-1
bys idhh: gen cum_sav_05_07=(act_sav_rate*L.act_sav_rate*L2.act_sav_rate)-1
bys idhh: gen cum_sav_06_07=(act_sav_rate*L.act_sav_rate)-1
**Summary stats**
su cum_sav_*
**Calculate the cumulative returns on the stock portfolio between 2000-2007**
/*Set the missing return_lag1 to zero when calculating the cumulative returns */
replace return_lag1=1 if mi(return_lag1)==1 /* assuming zero return */
**
bys idhh: gen cum_ret_00_07=(return_lag1*L.return_lag1*L2.return_lag1*L3.return_lag1*L4.return_lag1*L5.return_lag1*L6.return_lag1*L7.return_lag1)-1
bys idhh: gen cum_ret_01_07=(return_lag1*L.return_lag1*L2.return_lag1*L3.return_lag1*L4.return_lag1*L5.return_lag1*L6.return_lag1)-1
bys idhh: gen cum_ret_02_07=(return_lag1*L.return_lag1*L2.return_lag1*L3.return_lag1*L4.return_lag1*L5.return_lag1)-1
bys idhh: gen cum_ret_03_07=(return_lag1*L.return_lag1*L2.return_lag1*L3.return_lag1*L4.return_lag1)-1
bys idhh: gen cum_ret_04_07=(return_lag1*L.return_lag1*L2.return_lag1*L3.return_lag1)-1
bys idhh: gen cum_ret_05_07=(return_lag1*L.return_lag1*L2.return_lag1)-1
bys idhh: gen cum_ret_06_07=(return_lag1*L.return_lag1)-1
**Summary stats**
su cum_ret_*
**Winsorize the annual buy-and-hold returns**
gen ann_buy_hold=(return_lag1-1)
winsor ann_buy_hold if mi(ann_buy_hold)==0, gen(wann_buy_hold) p(.01)

**Calculate the average return rate between 2000-2007**
drop if year==1999
gen annual_ret=(return_lag1-1)
bys idhh: egen mean_return_00_07=mean(annual_ret)
su mean_return_00_07,d
winsor mean_return_00_07, gen(wmean_return_00_07) p(0.01)

**SEK Sum of Passive Changes in the Financial Portfolio**
su tot_pas_changes, d
winsor tot_pas_changes, gen(wtot_pas_changes) p(.01)
gen log_passive=log(tot_pas_changes+1)


**Generate Yearly Dummies for Having Positive Financial Assets**
forvalues y=1999(1)2007 {
gen fin_`y'=1 if hhfinw>0 & year==`y'
bys idhh: egen dfin_`y'=mean(fin_`y')
drop fin_`y'
 } 
**Keep Variables of Interest**
keep idhh year ann_buy_hold wann_buy_hold act_sav_rate_inc act_sav_rate_inc_00 cum_sav_* cum_ret_* dfin_* tot_diff_99_07 hhstocksmutual hhfinw mean_act_sav_rate_00_07 mean_return_00_07 wmean_return_00_07 tot_pas_changes wtot_pas_changes log_passive
**Keep only observations from 2007**
keep if year==2007
**Save the Working Sample as Stata format**
save "$projf/data/linda_ret_saving_230423.dta", replace
**Close the log-file
cap log close
 

*cf _all using "$projf/data/linda_ret_saving.dta", verbose all
*cf _all using "$projf/data/linda_ret_saving_200424.dta", verbose all

*use "$projf/data/linda_ret_saving_200424.dta", clear
*cf _all using "$projf/data/linda_ret_saving_230423.dta", verbose all
